const express = require("express");
const router = express.Router();
const dayjs = require("dayjs");
const utc = require("dayjs/plugin/utc");
const timezone = require("dayjs/plugin/timezone");

dayjs.extend(utc);
dayjs.extend(timezone);

let connectDAO = require("./connect.js");

// 登陆
router.get("/login", function (req, res) {
  const { PhoneNumber, Password } = req.query;
  const sql = `SELECT * FROM Officers WHERE PhoneNumber = '${PhoneNumber}' AND Password = '${Password}';`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        data: records[0],
      });
    } else {
      res.send({
        code: 201,
        msg: "账号或密码不正确",
      });
    }
  });
});

// 注册
router.post("/register", function (req, res) {
  const { Name, Department, PhoneNumber } = req.body;
  const sql = `INSERT INTO Officers (Name, Gender,Rank,Department,PhoneNumber, Permission,Password,HireDate) VALUES 
  ('${Name}', 'Male', '警员', '${
    Department || "派出所"
  }', '${PhoneNumber}', 'user','${PhoneNumber.slice(-6)}', '${dayjs().format(
    "YYYY-MM-DD"
  )}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 一键报警
router.post("/call_the_police", function (req, res) {
  const { OfficerID, LocationLat, LocationLng } = req.body;
  const sql = `INSERT INTO EmergencyAlert (OfficerID, LocationLat, LocationLng) VALUES ('${OfficerID}', '${LocationLat}','${LocationLng}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 我的一键报警记录
router.get("/my_call_the_police_records", function (req, res) {
  const { OfficerID } = req.query;
  const sql = `SELECT * FROM EmergencyAlert WHERE OfficerID = '${OfficerID}'`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        records,
        total: records.length,
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
        records,
      });
    }
  });
});

// 发布公告
router.post("/community_announcement", function (req, res) {
  const { Title, Content, Priority } = req.body;
  const sql = `INSERT INTO Announcement (Title, Content,Priority) VALUES ('${Title}', '${Content}','${Priority}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 获取公告
router.get("/community_announcement", function (req, res) {
  const sql = `SELECT * FROM Announcement ORDER BY CreateTime DESC`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        records,
        total: records.length,
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
        records,
      });
    }
  });
});

// 投诉建议
router.post("/complaints_suggestions", function (req, res) {
  const { Type, SubmitterID, SubmitterName, Content, ContactInfo } = req.body;
  const sql = `INSERT INTO ComplaintSuggestion (Type,SubmitterID, SubmitterName,ContactInfo,Content) VALUES ('${Type}', '${SubmitterID}','${SubmitterName}','${ContactInfo}','${Content}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 我的投诉建议
router.get("/my_complaints_suggestions", function (req, res) {
  const { SubmitterID } = req.query;
  const sql =
    "SELECT * FROM ComplaintSuggestion" +
    `${SubmitterID ? " WHERE SubmitterID = " + SubmitterID : ""}`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        records,
        total: records.length,
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
        records,
      });
    }
  });
});

// 回复投诉建议
router.put("/reply_complaints_suggestions", function (req, res) {
  const { ID, Response } = req.body;
  const sql = `UPDATE ComplaintSuggestion SET Status = 'resolved', Response = '${Response}', HandleTime = '${dayjs().format(
    "YYYY-MM-DD HH:mm:ss"
  )}' WHERE ID = '${ID}'`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

// 获取officers
router.get("/get_officers_records", function (req, res) {
  const { OfficerID } = req.query;
  const sql = `SELECT * FROM Officers ${
    OfficerID ? "WHERE OfficerID = " + OfficerID : ""
  }`;
  connectDAO
    .queryDataBySQL(sql, (records) => {
      if (records.length > 0) {
        res.send({
          code: 200,
          msg: "Success",
          records,
          total: records.length,
        });
      } else {
        res.send({
          code: 200,
          msg: "no data",
          records,
        });
      }
    })
    .catch((err) => {
      res.send({ code: 201, msg: "error", err });
    });
});

module.exports = router;
